package com.rykj.qxj.test.sql;

import java.io.ByteArrayOutputStream;
import java.io.PrintStream;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.util.TablesNamesFinder;

public class SqlTool {
	
	public static List<String> test_select_table(String sql)
			throws JSQLParserException {
		Statement statement = (Statement) CCJSqlParserUtil.parse(sql);
		Select selectStatement = (Select) statement;
		TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();
		List<String> tableList = tablesNamesFinder
				.getTableList(selectStatement);
		return tableList;
	}

	 
	 public static void main(String[] args) throws JSQLParserException {
		 String sqlStr="SELECT DATE_FORMAT(ddatetime, '%Y%m%m%h%i%s') AS ddateTime, obtid, t, sum(p), sum(p0) as sump0, u, wdidf, wdidd, wd2df, wd2dd, wd10df, wd10dd, wd3smaxdf, wd3smaxdd, wd3smaxtime, wd10maxdf, wd10maxdd, wd10maxtime, hourr, keyid FROM T_LOCALOBTMIND WHERE keyid > :keyId AND ((DATE_FORMAT(ddatetime, '%i') IN ('00', '05', '10', '15', '20', '25', '30', '35', '40', '45', '50', '55')) AND obtid IN ('G3735', 'G3521', 'G3540', 'G3556', 'G3641', 'G3567', 'G3536', 'G3526', 'G3636', 'G3643', 'G3544', 'G3566', 'G3640', 'G3666', 'G3525', 'G3533', 'G1142', 'X3668', 'G3598', 'G3704', 'G3668', 'G1125', 'G1179', 'G1189', 'G1192', 'G1195', 'G3512', 'G3541', 'G3563', 'G3578', 'G3585', 'G3627', 'G3645', 'G3674', 'G3677', 'G3680', 'G3723', 'G3724', 'G3725', 'G3730', 'G3794', 'G3782')) ORDER BY ddatetime";
		/* System.out.println(sqlStr);
		 List<String> tableNameBySql = SqlTool.test_select_table(sqlStr);
		 for (String string : tableNameBySql) {
			 System.out.println(string);
		}
		 String judge_type = judge_type(sqlStr);
		 System.out.println(judge_type);*/
		 
		 String sql="select name AS ASname, age As Asage, sex as assex, sacore,sum(score) as  sumScore";
		 List<String> responseParamBySql = getResponseParamBySql(sqlStr);
		 System.out.println(responseParamBySql.toString());
	}
	     // 验证sql语法正确性，返回错误信息
		// 传入 String sql
		// ***********返回错误信息such as： “错误单词” “line 1” “column 80”
		public static String judge_type(String sql) {
	 
			try {
				Statement statement = (Statement) CCJSqlParserUtil.parse(sql);
			} catch (JSQLParserException e) {
				ByteArrayOutputStream baos = new ByteArrayOutputStream();
				e.printStackTrace(new PrintStream(baos));
				String exception = baos.toString();
				String regEx = "Encountered(.*)";
				Pattern pat = Pattern.compile(regEx);
				Matcher mat = pat.matcher(exception);
				while (mat.find()) {
					exception = mat.group(1);
				}
				// System.out.println(exception);
				String line = "";
				String regEx2 = "line (.*),";
				pat = Pattern.compile(regEx2);
				mat = pat.matcher(exception);
				while (mat.find()) {
					line = mat.group(1);
				}
				// System.out.println(line);
	 
				int line_num = Integer.valueOf(line).intValue();
				int indexofcolumn = exception.indexOf("column");
				String errornumber = exception.substring(indexofcolumn + 7,
						exception.length() - 1);
				int error_num = Integer.valueOf(errornumber).intValue();
				System.out.println(error_num);
	 
				String ERROR_location = "";
				if (error_num != 1) {
					String sql_sub = sql.substring(0, error_num - 2); // 发生错误位置前面的字符串
					// 错误信息单词往往处于错误位置的前一个地方单词
					// 获取错误位置两个前面两个空格之间的单词，并保存
					sql_sub = new StringBuilder(sql_sub).reverse().toString();
					int indexofspace = sql_sub.indexOf(" ");
					String sql_error = sql_sub.substring(0, indexofspace);
					sql_error = new StringBuilder(sql_error).reverse().toString();
					ERROR_location = "\"" + sql_error + "\"" + " at line "
							+ line_num + " at column " + error_num;
				} else {
					int indexofspace = sql.indexOf(" ");
					String sql_error = sql.substring(0, indexofspace);
					ERROR_location = "\"" + sql_error + "\"" + " at line "
							+ line_num + " at column " + error_num;
				}
				return ERROR_location; // 错误信息的返回
			}
			String result = "correct";
			return result; // Jsql可以解析，返回correct
		}
		
		private static List<String> getResponseParamBySql(String sql) {
			/*String str[] = sql.split(" ");
			List<String> responseName = new ArrayList<String>();
			List<String> resultName = new ArrayList<String>();
			for (int i = 0; i < str.length; i++) {
				if ("as".equalsIgnoreCase(str[i])) {
					responseName.add(str[i + 1]);
				}
			}
			for (int j = 0; j < responseName.size(); j++) {
				if (responseName.get(j).contains(",")) {
					String[] split = responseName.get(j).split(",");
					resultName.add(split[0]);
				} else {
					resultName.add(responseName.get(j));
				}
			}
			return resultName;*/
			List<String> resultList=new ArrayList<String>();
		    String column = sql.toLowerCase().split("from")[0].trim();
		    String[] columns = column.split(",");
		    for(String str : columns){
		        str = str.trim().replaceAll("select\\s+","");
		        str = str.replaceAll("`","");
		        if(str.indexOf("(")>=0 && str.indexOf(")")==-1) {
		        	continue;
		        }else if(str.indexOf("as")>=0 || str.indexOf("AS")>=0) {
		        	String[] asStr = str.split(" ");
		        	for (int i = 0; i < asStr.length; i++) {
						if ("as".equalsIgnoreCase(asStr[i])) {
							resultList.add(asStr[i + 1]);
						}
					}
		        //}
		        //else if(str.indexOf("as")>=0){
		           //str = str.replaceAll("^([^s]*)as\\s+","");
		          // resultList.add(str);
		        }else if(str.indexOf(" ")>=0){
		           str = str.replaceAll("^([^s]*)\\s+","");
		           resultList.add(str);
		        }else if(str.indexOf(".")>=0){
		            str = str.replaceAll("^([^s]*)\\.","");
		            resultList.add(str);
		        }else {
		        	resultList.add(str);
		        }
		    }
		    return resultList;
		}
		
}
